-- ------------------------------------------------------------------------------- 
--   Table : METTEUR EN SCENE
-- ------------------------------------------------------------------------------- 

CREATE OR REPLACE TRIGGER TI_METTEUR_EN_SCENE 
BEFORE INSERT ON METTEUR_EN_SCENE FOR EACH ROW
BEGIN
    SELECT 
		SEQ_METTEUR_EN_SCENE.nextval INTO :NEW.NUM_MET_SCENE
    FROM DUAL;  
END;
/

-- ------------------------------------------------------------------------------- 
--   Table : ARTISTE
-- ------------------------------------------------------------------------------- 

CREATE OR REPLACE TRIGGER TI_ARTISTE 
BEFORE INSERT ON ARTISTE FOR EACH ROW
BEGIN
    SELECT 
		SEQ_ARTISTE.nextval INTO :NEW.NUM_ARTISTE
    FROM DUAL;  
END;
/

-- ------------------------------------------------------------------------------- 
--   Table : SALLE
-- ------------------------------------------------------------------------------- 

CREATE OR REPLACE TRIGGER TI_SALLE
BEFORE INSERT ON SALLE FOR EACH ROW
BEGIN
    SELECT 
		SEQ_SALLE.nextval INTO :NEW.NUM_SALLE
    FROM DUAL;  
END;
/

-- ------------------------------------------------------------------------------- 
--   Table : TYPE_RETRAIT
-- ------------------------------------------------------------------------------- 

CREATE OR REPLACE TRIGGER TI_TYPE_RETRAIT
BEFORE INSERT ON TYPE_RETRAIT FOR EACH ROW
BEGIN
    SELECT 
		SEQ_TYPE_RETRAIT.nextval INTO :NEW.NUM_TYPERETRAIT
    FROM DUAL;  
END;
/

-- ------------------------------------------------------------------------------- 
--   Table : AUTEUR
-- ------------------------------------------------------------------------------- 

CREATE OR REPLACE TRIGGER TI_AUTEUR 
BEFORE INSERT ON AUTEUR FOR EACH ROW
BEGIN
    SELECT 
		SEQ_AUTEUR.nextval INTO :NEW.NUM_AUTEUR
    FROM DUAL;  
END;
/

-- ------------------------------------------------------------------------------- 
--   Table : REPRESENTATION
-- ------------------------------------------------------------------------------- 

create or replace trigger TI_REPRESENTATION
before insert on REPRESENTATION 
for each row
begin
	-- get nom spectacle
	
	select
		  spect.nom_spectacle into :new.nom_spectacle
	from
		spectacle spect
	where 
		spect.num_spectacle = :new.num_spectacle;

	-- get libelle du type de spectacle :
	
	select
		typ.lib_typespect into :new.lib_typespect
	from 
		spectacle spect
			inner join type_spectacle typ
			  on typ.num_typespect = spect.num_typespect
	where
		spect.num_spectacle = :new.num_spectacle;

	-- get nb_places_rest
	
	select 
		nb_places_salle into :new.nb_places_rest 
	from 
		salle 
	where 
		num_salle = :new.num_salle;
		
end;
/

create or replace trigger TD_REPRESENTATION
after delete on REPRESENTATION 
for each row
begin

     -- Supprimer les occurrences correspondantes de la table TARIF_REPRESENTATION.

     delete from TARIF_REPRESENTATION
     where
          TARIF_REPRESENTATION.NUM_SPECTACLE = :old.NUM_SPECTACLE
	 and
          TARIF_REPRESENTATION.NUM_REPRES = :old.NUM_REPRES;

end;
/


-- ------------------------------------------------------------------------------- 
--   Table : TARIF_REPRESENTATION
-- ------------------------------------------------------------------------------- 

create or replace trigger TI_TARIF_REPRESENTATION
before insert on TARIF_REPRESENTATION 
for each row
begin
	-- get nombre de places restantes :
	
	select
		cat.nb_places_cat into :new.nb_places_rest
	from
		categorie_place cat
	where
		cat.num_salle = :new.num_salle
		and cat.num_cat_place = :new.num_cat_place;
		
end;
/

-- ------------------------------------------------------------------------------- 
--   Table : SPECTACLE
-- ------------------------------------------------------------------------------- 

CREATE OR REPLACE TRIGGER TI_SPECTACLE 
BEFORE INSERT ON SPECTACLE FOR EACH ROW
BEGIN
    SELECT SEQ_SPECTACLE.nextval INTO :NEW.NUM_SPECTACLE
    FROM DUAL;  
END;
/

CREATE OR REPLACE TRIGGER TU_SPECTACLE
BEFORE UPDATE ON SPECTACLE 
for each row
BEGIN
	if :old.nom_spectacle != :new.nom_spectacle then
		update 
			representation
		set 
			nom_spectacle = :new.nom_spectacle
		where 
			num_spectacle = :new.num_spectacle;
	end if;
END;
/

create or replace trigger TD_SPECTACLE
after delete on SPECTACLE for each row
declare 
	numrows INTEGER;
begin

     -- Supprimer les occurrences correspondantes de la table TARIF_SALLE.

     delete from TARIF_SALLE
     where
          TARIF_SALLE.NUM_SPECTACLE = :old.NUM_SPECTACLE;
     -- Supprimer les occurrences correspondantes de la table JOUER.

     delete from JOUER
     where
          JOUER.NUM_SPECTACLE = :old.NUM_SPECTACLE;
     -- Supprimer les occurrences correspondantes de la table TARIF_SPECTACLE.

     delete from TARIF_SPECTACLE
     where
          TARIF_SPECTACLE.NUM_SPECTACLE = :old.NUM_SPECTACLE;
     -- Interdire la suppression d'une occurrence de SPECTACLE s'il existe des
     -- occurrences correspondantes de la table REPRESENTATION.

     select count(*) into numrows
     from REPRESENTATION
     where
          REPRESENTATION.NUM_SPECTACLE = :old.NUM_SPECTACLE;
     if (numrows > 0) then
          raise_application_error(
          -20001,
          'Impossible de supprimer "SPECTACLE". Des occurrences de "REPRESENTATION" existent.');
     end if;
     -- Supprimer les occurrences correspondantes de la table ECRIRE.

     delete from ECRIRE
     where
          ECRIRE.NUM_SPECTACLE = :old.NUM_SPECTACLE;
     -- Supprimer les occurrences correspondantes de la table METTRE_EN_SCENE.

     delete from METTRE_EN_SCENE
     where
          METTRE_EN_SCENE.NUM_SPECTACLE = :old.NUM_SPECTACLE;

end;
/

-- ------------------------------------------------------------------------------- 
--   Table : PLACE
-- ------------------------------------------------------------------------------- 

create or replace TRIGGER TI_PLACE 
BEFORE INSERT ON PLACE FOR EACH ROW
BEGIN
    :NEW.NUM_SALLE2 := :NEW.NUM_SALLE; 
END;
/

-- ------------------------------------------------------------------------------- 
--   Table : LIGNE RESERVATION
-- ------------------------------------------------------------------------------- 

create or replace TRIGGER TI_LIGNERESERVATION
AFTER INSERT ON LIGNE_RESERVATION FOR EACH ROW
BEGIN

-- update tarif_representation

    update
		tarif_representation tar
	set
		tar.nb_places_rest = (nb_places_rest - 1)
	where
		tar.num_spectacle = :new.num_spectacle
	and
		tar.num_repres = :new.num_repres
	and
		tar.num_salle = :new.num_salle
	and
		tar.num_cat_place = (select pla.num_cat_place from place pla where num_salle = :new.num_salle and num_place = :new.num_place);

-- update representation
	
	update
		representation rep
	set
		rep.nb_places_rest = (nb_places_rest - 1)
	where
		rep.num_spectacle = :new.num_spectacle
	and
		rep.num_repres = :new.num_repres;
		
END;
/

create or replace TRIGGER TI_LIGNERESERVATION_CODE
BEFORE INSERT ON LIGNE_RESERVATION FOR EACH ROW
BEGIN
	
	select
		substr(DBMS_CRYPTO.HASH (utl_raw.cast_to_raw(:new.num_repres || :new.num_place || :new.num_spectacle || 'OLK35c8'), 2), 1, 20) into :new.code_verif
	from
		dual;
		
END;
/